package com.yricky.memoria.data

import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import com.yricky.memoria.utils.UID
import java.io.File

/**
 * @author Yricky
 * @date 2021/12/3
 */
class TagManager(
    private val context: Context,
    private val dir:File,
    val tagWhich:String
    ) {

    fun linkTag(tagName: String,toWhich:String):Boolean{
        var execResult = false

        if(tagCache[tagName]?.let { relationshipTable.contains(db, it, toWhich) } == true){
            // 该link已经存在    todo?.返回一些信息以告知界面link已存在
            return false
        }

        db.transaction{
            // 先检查标签是否存在，若不存在则先添加标签
            if(tagCache[tagName] != null || addTag(tagName)){
                // 添加标签与目标uid的关联
                db.execSQL(
                    "insert or replace into ${relationshipTable.NAME}(${TUID},${tagWhich}) values(?,?)",
                    arrayOf(tagCache[tagName], toWhich)
                )
                execResult = true
            }
        }

        return execResult
    }

    fun unlinkTag(tagName: String,toWhich:String){
        // todo?.当前标签已下无链接的which时是否删除标签？
        val tUid = tagCache[tagName]
        if(tUid != null){
            db.delete(
                relationshipTable.NAME,
                "${TUID}=? and ${tagWhich}=?",
                arrayOf(tUid, toWhich)
            )
        }
    }

    fun getMyTags(which:String):List<String>{
        val tagNameList = ArrayList<String>()
        db.transaction {
            db.query(
                relationshipTable.NAME,
                arrayOf(TUID, tagWhich),
                "$tagWhich = ?",
                arrayOf(which),
                null,
                null,
                tagWhich,
                null
            ).let{ cursor ->
                val tUidIndex = cursor.getColumnIndex(TUID)
                if(
                    tUidIndex >= 0 &&
                    cursor.count > 0
                ){
                    cursor.moveToFirst()
                    // 遍历取到的tUid值，将其挨个转换为tagName
                    (0 until cursor.count).forEach { _ ->
                        // 根据tUid获取tagName（过滤出tagCache中值等于tUid的，然后取其键）
                        tagCache.filterValues{ tagUid ->
                            tagUid == cursor.getString(tUidIndex)
                        }.keys.firstOrNull()?.let{ tagName ->
                            tagNameList.add(tagName)
                        }

                        cursor.moveToNext()
                    }
                }
                cursor.close()
            }
        }
        return tagNameList
    }

    fun getRelatedWhichs(tagName:String):List<String>{
        val tUid = tagCache[tagName]

        return db.query(
            relationshipTable.NAME,
            arrayOf(TUID, tagWhich),
            "$TUID = ?",
            arrayOf(tUid),
            null,
            null,
            tagWhich,
            null
        ).use{
            val list = ArrayList<String>()

            val whichIndex = it.getColumnIndex(tagWhich)
            if(
                whichIndex >= 0 &&
                it.count > 0
            ){
                it.moveToFirst()
                (0 until it.count).forEach { _ ->
                    list.add(it.getString(whichIndex))
                    it.moveToNext()
                }
            }

            list
        }
    }

    fun unlinkMyTags(which:String){
        tagCache.remove(which)?.let {
            db.delete(
                relationshipTable.NAME,
                "${tagWhich}=?",
                arrayOf(it)
            )
        }
    }

    fun addTag(tag:String):Boolean{
        if(tag.length > 40 || tagTable.contains(db,tag))
            return false
        val uid = UID.subjectTag()
        db.execSQL(
            "insert or replace into ${tagTable.NAME}(${TUID},${TNAME}) values(?,?)",
            arrayOf(uid,tag)
        )
        tagCache[tag] = uid
        return true
    }

    fun delTag(tagName:String){
        db.transaction {
            delete(
                tagTable.NAME,
                "${TNAME}=?",
                arrayOf(tagName)
            )
            delete(
                relationshipTable.NAME,
                "${TUID}=?",
                arrayOf(tagCache.remove(tagName))
            )
            tagCache.remove(tagName)
        }
    }

    fun modTag(tagNameOld:String,tagNameNew:String){
        val uid = tagCache[tagNameOld]
        if(tagNameNew.length < 40
            && uid != null
            && tagCache[tagNameNew] == null){
            db.execSQL(
                "insert or replace into ${tagTable.NAME}(${TUID},${TNAME}) values(?,?)",
                arrayOf(uid,tagNameNew)
            )
            tagCache[tagNameNew] = uid
            tagCache.remove(tagNameOld)
        }
    }

    fun getTags(filter:String = ""):List<String>{
        return db.query(
            tagTable.NAME,
            arrayOf(TUID, TNAME),
            "$TNAME like ?",
            arrayOf("%$filter%"),
            null,
            null,
            TNAME,
            null
        ).use{
            tagCache.clear()
            val list = ArrayList<String>()
            val colIndex = it.getColumnIndex(TNAME)
            val uidIndex = it.getColumnIndex(TUID)
            if(colIndex >= 0
                && uidIndex >= 0
                && it.count > 0){
                it.moveToFirst()
                (0 until it.count).forEach { _ ->
                    list.add(it.getString(colIndex))
                    tagCache[it.getString(colIndex)] = it.getString(uidIndex)
                    it.moveToNext()
                }
            }
            list
        }
    }


    companion object{
        const val TUID = "tUid"
        const val TNAME = "name"
    }
    private val db:SQLiteDatabase get() = dbHelper.db

    private val tagCache:HashMap<String/* name */,String/* uid */> = HashMap()
    private val tagTable = object{
        val NAME = "tUid_tName"
        fun contains(db: SQLiteDatabase, tagName:String):Boolean{
            return db.query(
                NAME,
                arrayOf(TNAME),
                "$TNAME like ?",
                arrayOf(tagName),
                null,
                null,
                TNAME,
                "1"
            ).use {
                it.count > 0
            }
        }

        fun initIn(db: SQLiteDatabase){
            db.execSQL("create table $NAME(${TUID} text primary key not null,${TNAME} text not null)")
        }
    }

    private val relationshipTable = object{
        val NAME = "tUid_$tagWhich"
        fun contains(db: SQLiteDatabase, tUid:String, whichUid:String):Boolean{
            return db.query(
                NAME,
                arrayOf(TUID, tagWhich),
                "$TUID like ? and $tagWhich like ?",
                arrayOf(tUid, whichUid),
                null,
                null,
                null,
                "1"
            ).use {
                it.count > 0
            }
        }

        fun initIn(db: SQLiteDatabase){
            db.execSQL("create table $NAME(${TUID} text not null,${tagWhich} text not null)")
        }
    }

    private val dbHelper = object : SQLiteOpenHelper(
        context,
        File(dir,"tag_$tagWhich.db").path,
        null,
        WorkSpaceModel.DB_VERSION
    ){
        val db by lazy { writableDatabase }

        override fun onCreate(db: SQLiteDatabase?) {
            db?.transaction {
                tagTable.initIn(this)
                relationshipTable.initIn(this)
            }
        }

        override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {

        }

    }
}